
CLASS lcl_excel DEFINITION .

  PUBLIC SECTION .
    CLASS-DATA: template_id TYPE string VALUE 'template_100' .
    METHODS:
      download_template,
      wait_and_indicate,
      export_excel IMPORTING io_data TYPE REF TO lcl_data,
      open_excel,
      close_excel,
      run_marco IMPORTING process_type TYPE char10 OPTIONAL,
      add_blank_sheet IMPORTING sheet_name type char10 ,
      paste2sheet IMPORTING sheet_name TYPE char10
                            col        TYPE i
                            row        TYPE i.
  PRIVATE SECTION .
    DATA: mt_budget TYPE TABLE OF zfit318_b,
          ms_budget LIKE LINE OF mt_budget.
    DATA: mv_excel  TYPE ole2_object,     " Excel object
          mv_books  TYPE ole2_object,     " list of workbooks
          mv_book   TYPE ole2_object,     " workbook
          mv_sheets TYPE ole2_object,
          mv_sheet  TYPE ole2_object,
          mv_range  TYPE ole2_object,
          mv_cell   TYPE ole2_object.
    DATA: mv_index TYPE i.
    DATA: BEGIN OF l_str ,
            line TYPE char1024,
          END OF l_str .
    DATA: lt_str LIKE TABLE OF l_str.
    DATA: mv_template_file(65) .

ENDCLASS .



CLASS lcl_excel IMPLEMENTATION .

  METHOD download_template .

    DATA: lv_name      TYPE string,
          lv_dir       TYPE string,
          lv_full_path TYPE string.

    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        default_extension    = 'xls'        "FILE format
        default_file_name    = 'AA'   "default name
      CHANGING
        filename             = lv_name     "default file download path
        path                 = lv_dir       "file save path
        fullpath             = lv_full_path "module save path
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.

    IF lv_full_path = ''  OR lv_dir IS INITIAL.
      MESSAGE '请选择下载路径' TYPE 'S' DISPLAY LIKE 'E'.
      RETURN.
    ENDIF.

    DATA: lv_destination TYPE rlgrap-filename,
          ls_objdata     TYPE wwwdatatab.
    lv_destination = lv_full_path.
    SELECT SINGLE relid,
                  objid
             FROM wwwdata
             INTO CORRESPONDING FIELDS OF @ls_objdata
            WHERE srtf2 = 0
              AND relid = 'MI'
              AND objid = @template_id.
    IF sy-subrc <> 0 OR ls_objdata-objid = space.
      MESSAGE '模板不存在，请上载模板后再进行下载' TYPE 'S' DISPLAY LIKE 'E'.
      LEAVE LIST-PROCESSING.
    ENDIF.

    DATA: lv_temp_file(65).
    DATA: lv_key TYPE wwwdatatab .
    lv_key-relid = 'MI' .
    lv_key-objid = template_id .

    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lv_key
        destination = lv_destination
      CHANGING
        temp        = mv_template_file.

  ENDMETHOD.

  METHOD export_excel .

    download_template( ) .

    open_excel( ) .

    loop  at io_data->lt_excel into data(ls_excel).
        add_blank_sheet( ls_excel-sheetname ).
        lt_str = io_data->join_by_data( ls_excel-data ).
        paste2sheet( sheet_name = |{ ls_excel-sheetname }| col = 1 row =  2 )
    endloop.

    run_marco(  ) .

    close_excel( ) .

    LEAVE LIST-PROCESSING .

  ENDMETHOD.


  METHOD open_excel .

    CREATE OBJECT mv_excel 'EXCEL.APPLICATION'.

    SET PROPERTY OF mv_excel 'Visible' = 1 .
    SET PROPERTY OF mv_excel 'DISPLAYALERTS' = 0 .

    CALL METHOD OF
      mv_excel
        'Workbooks' = mv_books.

    CALL METHOD OF
       mv_books
        'Open' = mv_book
      EXPORTING
        #1 = mv_template_file .

    CALL METHOD OF
      mv_book
        'Worksheets' = mv_sheets.

  ENDMETHOD.

  METHOD add_blank_sheet .

    CALL METHOD OF mv_sheets 'Add' .

    mv_index = mv_index + 1 .
    DATA(new_sheet) = |Sheet{ mv_index }|.

    CALL METHOD OF
        mv_book
        'WORKSHEETS' = mv_sheet
        EXPORTING
          #1 = new_sheet.

    CALL METHOD OF mv_sheet 'Activate'.

    SET PROPERTY OF mv_sheet 'Name' = sheet_name .

  ENDMETHOD.

  METHOD paste2sheet .

    CALL FUNCTION 'CONTROL_FLUSH'
      EXCEPTIONS
        cntl_system_error = 1
        cntl_error        = 2
        OTHERS            = 3.

    CALL METHOD OF
        mv_book
        'WORKSHEETS' = mv_sheet
        EXPORTING
          #1 = sheet_name.

    CALL METHOD OF mv_sheet 'Activate'.

    " A2 单元格
    CALL METHOD OF mv_sheet
       'Cells' = mv_cell
      EXPORTING
        #1 = row
        #2 = col  .

    CALL METHOD OF mv_cell 'Select' .

    DATA: lv_rc TYPE i .
    CALL METHOD cl_gui_frontend_services=>clipboard_export       "Paste to Excel from Excel_tab
      IMPORTING
        data                 = lt_str
      CHANGING
        rc                   = lv_rc
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.

    CALL METHOD OF mv_sheet 'Paste' .

    CLEAR: lt_str .

  ENDMETHOD.

  METHOD run_marco .

    " 执行宏
    CALL METHOD OF mv_excel 'RUN'
      EXPORTING
        #1 = `执行宏`
        #2 = process_type.

  ENDMETHOD.

  METHOD close_excel .

    CALL METHOD OF mv_book 'SAVE' .
    CALL METHOD OF mv_book 'Quit' .

  ENDMETHOD.

  METHOD wait_and_indicate .

    CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
      EXPORTING
        percentage = 1
        text       = '正在生成EXCEL文件，请稍候'.

  ENDMETHOD.


ENDCLASS .